
  /***********************************************************************************************

    Object:     jsl.DataSource.CDb
    Implements: jsl.DataSource.Connector

  ************************************************************************************************/

	//!
  //-------------------------------------------------------------------------------
  jsl.error("QueryError", jsl.ParamError, "dst.db.BadQuery")//!
  //-------------------------------------------------------------------------------
  jsl.DataSource.CDb = {

    //private data
    //-------------------------------------------------------------------------------
    __name: "CDb",
    __hds:  { any: 1, json: 1, xml: 1, blob: 1, d: 1, b: 1 }, //high data

    __ds:   null,
    __md:   null,
    __pk:   null,
    __q:    null,
    __dset: null,

    //-------------------------------------------------------------------------------
    type: function() {
      return "db"
    },
    //-------------------------------------------------------------------------------
    define: function(ds, options) {
      try {
        jsl.require("file")
        try      { ds.path = jsl.File.nativePath(ds.location) }
        catch(e) { throw jsl.ParamError("BadValue:options.location:path, url") }
      }
      catch(e) {
        jsl.throwError(e, this, arguments)
      }
    },
    /*
      custom options:
        query
    */
    //-------------------------------------------------------------------------------
    connect: function(ds, tid, options) {
    	try {
        var q, q2, Ds, md, nm, pk, a, f, dset, dmd

        q = options.query != null ? options.query : options.option

        //!
        if(!tid && q == null)                                                     throw jsl.ParamError("Required:id(table) | options.query")
        if( tid && q != null)                                                     throw jsl.ParamError("NotRequired:options.query")
        if(q != null)                                                             jsl.validate(q, "s+", "options.query")
        if(options.metadata && options.metadata.reals)                            throw jsl.ParamError("e.dst.db.InvalidMetadata:options.metadata")

        if(!jsl.Url.valid(ds.path))
        if(!jsl.DataSource.__ni_db_exists(ds.path))                               throw jsl.ParamError("NotFound:id(datasource)")
        if(tid && !jsl.DataSource.__ni_db_exists(ds.path, tid))                   throw jsl.ParamError("NotFound:id(table)")

        if(q && !q.match(/^\s*SELECT/i))                                          throw jsl.QueryError("BadValue:options.query:SELECT")
        if(q && q.replace(/'([^']+|'')*'|"([^"]+|"")*"/g, "").indexOf(";") > -1)  throw jsl.MisuseError("e.dst.db.NoMultipleQueries")//!

        //-------------------------------------------------------------------------------
        Ds  = jsl.DataSource
        md  = this.__getmd(ds, tid, q)
        nm  = md.length

        if(tid)
          q2 = "SELECT * FROM "  + tid
        else {
          md.wr = md.wr && q.replace(/'([^']+|'')*'|"([^"]+|"")*"/g, "").search(/[\s)]+(UNION|EXCEPT|INTERSECT|GROUP\s+BY)[\s(]+/i) === -1
          q2 = q
        }

        if(!options.readonly && md.wr) {

          pk = []
          for(a = 0; a < nm; a++)
            if(md[a].key) pk.push(md[a].field)

          if(pk.length)
            this.__pk = pk
          else {
            if(tid)             q2 = "SELECT oid AS __oid, * FROM "  + tid
            else                q2 = q.replace(/SELECT/i, "SELECT oid AS __oid,")

            this.__pk = [ "__oid" ]
          }
        }

        dmd = md
        if(options.metadata) dmd = dmd.concat(options.metadata)

        dset      = new Ds.DataSet(dmd, { readonly: options.readonly })
        dset.__ds = Ds.__ni_db_query(ds.path, q2)

        if(options.onReady) {
        	f = options.onReady
          jsl.delay(function() { f(dset) }, 0)
        }

        this.__ds   = ds
        this.__md   = md
        this.__q    = q2
        this.__dset = dset
        return dset
      }
      catch(e) {
        jsl.throwError(e, this, arguments)
      }
    },
    //-------------------------------------------------------------------------------
    execute: function(ds, command, onReady) {
    	try {
        var sql, v, dt, Ds, params

        //!
        jsl.validate(command, "t(s+,o)", "command")//!

        sql = jsl.isString(command) ? command : command.sql//!
        if(sql == null) throw jsl.ParamError("NullValue:command.sql")
        jsl.validate(sql, "s+", "command.sql")
        if(ds.readonly && sql.search(/^\s*(EXISTS|SELECT|ANALYZE|EXPLAIN|PRAGMA)/i) === -1) throw jsl.MisuseError("e.ds.ReadOnly")//!

        v       = null
        Ds      = jsl.DataSource
        params  = jsl.isString(command) ? {} : jsl.Object.clone(command)

        delete params.sql
        sql = sql.replace(/('(?:''|[^']+)*'|"(?:""|[^"]+)*")|@([^@\s()+\-*\/"',=!><%?.\[\]\\{}&|^]+)/g, function(tk, g1, g2) {
          var id, v

          if(g2) {//!
            if(!params.hasOwnProperty(g2)) throw jsl.ParamError("e.dst.db.InvalidMarker", [ g2 ])//!

            v = Ds.__toNative(params[g2])

            if(v == null)                   return "NULL"
            else if(typeof v === "string")  return "'" + v.replace(/'/g, "''") + "'"
            else                            return +v + ""
          }
          else
            return g1
        })

        //1-EXISTS TABLE
        //-------------------------------------------------------------------------------
        if(sql.match(/^\s*EXISTS\s+TABLE/i)) {

          dt = sql.match(/^\s*EXISTS\s+TABLE\s+(\S+)\s*$/i)//!
          if(!dt) throw jsl.QueryError()//!
          v = Ds.__ni_db_exists(ds.path, dt[1])
        }
        //2-EXISTS DATABASE
        //-------------------------------------------------------------------------------
        else if(sql.match(/^\s*EXISTS\s+DATABASE/i)) {

          dt = sql.match(/^\s*EXISTS\s+DATABASE\s*$/i)//!
          if(!dt) throw jsl.QueryError()//!
          v = Ds.__ni_db_exists(ds.path)
        }
        //3-CREATE DATABASE
        //-------------------------------------------------------------------------------
        else if(sql.match(/^\s*CREATE\s+DATABASE/i)) {

          dt = sql.match(/^\s*CREATE\s+DATABASE\s*$/i)//!
        	if(!dt) throw jsl.QueryError()
          if(Ds.__ni_db_exists(ds.path)) throw jsl.MisuseError("AlreadyCreated", [ "datasource(" + ds.id + ")" ])//!
          Ds.__ni_db_create(ds.path)
        }
        //4-DROP DATABASE
        //-------------------------------------------------------------------------------
        else if(sql.match(/^\s*DROP\s+DATABASE/i)) {

          dt = sql.match(/^\s*DROP\s+DATABASE\s*$/i)//!
          if(!dt) throw jsl.QueryError()//!
        	Ds.__ni_db_remove(ds.path)
        }
        //5-STANDARS SQLITE SQL
        //-------------------------------------------------------------------------------
        else {//!
          if(!Ds.__ni_db_exists(ds.path)) throw jsl.MisuseError("NotFound", [ "datasource(" + ds.id + ")" ])//!
        	v = Ds.__ni_db_query(ds.path, sql)
        }

        if(onReady)
          jsl.delay(function() { onReady(v) }, 0)

        return v
      }
      catch(e) {
        jsl.throwError(e, this, arguments)
      }
    },
    //-------------------------------------------------------------------------------
    commit: function(rows, onReady) {
      try {
      	var Cn

      	Cn = this
        if(onReady) jsl.delay(function() { Cn.__commit(rows, onReady) }, 0)
        else        this.__commit(rows)
      }
      catch(e) {
        jsl.throwError(e, this, arguments)
      }
    },
    //-------------------------------------------------------------------------------
    refresh: function(onReady) {
      try {
      	var rs, f, Cn

      	Cn = this
      	f  = function() {
        	rs = jsl.DataSource.__ni_db_query(Cn.__ds.path, Cn.__q)
        	onReady && onReady(rs)
      	}

      	if(onReady)  jsl.delay(f, 0)
      	else         f()
      	return rs
      }
      catch(e) {
        jsl.throwError(e, this, arguments)
      }
    },
    //private
    //-------------------------------------------------------------------------------
    __commit: function(rows, onReady) {
    	try {
        var sqls, md, tid, nm, pk, rb, lid, rs, rs2, a, r, nr, b, sql, Ds, nk, v, hds, ids

        if(this.__md.wr) {

          sqls  = []
          md    = this.__md
          nm    = md.length
          pk    = this.__pk
          pk    = pk.length === 1 && pk[0] === "__oid" ? null : pk
          nk    = pk && pk.length
          tid   = md.tid
          hds   = this.__hds

          //1-begin transaction
          //-----------------------------------------------
          if(pk || !rows.added.length)
            this.execute(this.__ds, "BEGIN")
          else {
            this.execute(this.__ds, "BEGIN IMMEDIATE")
            lid = this.execute(this.__ds, "SELECT max(rowid) AS id FROM " + tid)
            lid = lid.length ? lid[0].id : 0
          }

          rb = true
          Ds = jsl.DataSource

          //2-sql insert
          //-----------------------------------------------
          rs = rows.added
          for(a = 0, nr = rs.length; a < nr; a++) {

            r   = rs[a]
            ids = {}
            sql = "INSERT INTO " + tid + (pk ? " (" : " (rowid,")

            for(b = 0; b < nm; b++)
              if(md[b].wr && ! ids[md[b].field]) {
                sql += md[b].field + ","
                ids[md[b].field] = true
              }

            ids = {}
            sql = sql.slice(0, -1) + ") VALUES ("

            sql += (pk ? "" : ++lid + ",")
            for(b = 0; b < nm; b++)
              if(md[b].wr && ! ids[md[b].field]) {

                v = hds[md[b].type] ? Ds.__toNative(r[md[b].id], md[b].type) : r[md[b].id]
                ids[md[b].field] = true

                if(v == null)                   sql += "NULL,"
                else if(md[b].type === "blob")  sql += "X'" + v + "',"
                else if(typeof v === "string")  sql += "'"  + v.replace(/'/g, "''") + "',"
                else                            sql += +v   + ","
              }

            sql = sql.slice(0, -1) + ")"
            sqls.push(sql)
          }

          //3-sql delete
          //-----------------------------------------------
          rs = rows.removed
          for(a = 0, nr = rs.length; a < nr; a++) {

            sql = "DELETE FROM " + tid + " WHERE "
            r   = rs[a]

            if(pk) {
              for(b = 0; b < nk; b++) {
                sql += pk[b] + "="

                v = hds[md[b].type] ? Ds.__toNative(r[pk[b]]) : r[pk[b]]

                if(v == null)                   sql += "NULL AND "
                else if(typeof v === "string")  sql += "'"  + v.replace(/'/g, "''") + "' AND "
                else                            sql += +v   + " AND "
              }

              sql = sql.slice(0, -5)
            }
            else
              sql += "rowid=" + rs[a].__oid

            sqls.push(sql)
          }

          //4-sql update
          //-----------------------------------------------
          rs  = rows.updated
          rs2 = rows.old
          for(a = 0, nr = rs.length; a < nr; a++) {

            r   = rs[a]
            ids = {}
            sql = "UPDATE " + tid + " SET "

            for(b = 0; b < nm; b++)
              if(md[b].wr && !ids[md[b].field]) {

                sql += md[b].field + "="
                v    = hds[md[b].type] ? Ds.__toNative(r[md[b].id]) : r[md[b].id]
                ids[md[b].field] = true

                if(v == null)                   sql += "NULL,"
                else if(md[b].type === "blob")  sql += "X'" + v + "'" + ","
                else if(typeof v === "string")  sql += "'"  + v.replace(/'/g, "''") + "'" + ","
                else                            sql += +v   + ","
              }

            sql = sql.slice(0, -1) + " WHERE "

            if(pk) {
              for(b = 0; b < nk; b++) {
                sql += pk[b] + "="

                v = hds[md[b].type] ? Ds.__toNative(rs2[a][pk[b]]) : rs2[a][pk[b]]

                if(v == null)                   sql += "NULL AND "
                else if(typeof v === "string")  sql += "'"  + v.replace(/'/g, "''") + "' AND "
                else                            sql += +v   + " AND "
              }

              sql = sql.slice(0, -5)
            }
            else
              sql += "rowid=" + rs[a].__oid

            sqls.push(sql)
          }

          //5-sql commit
          //-----------------------------------------------
          this.execute(this.__ds, sqls.join(";") + ";COMMIT")
          rb = false

          onReady && onReady()
        }
        else
          jsl.console.info("dst.db.SilentCommit", [ this.__ds.path ])
    	}
    	catch(e) {
        if(rb) this.execute(this.__ds, "ROLLBACK")
        throw e
    	}
    },
    //-------------------------------------------------------------------------------
    __getmd: function(ds, tid, q) {
      var dt, md, dta, t, v

      if(tid) q = "SELECT * FROM "  + tid + "  LIMIT 0"
      else    q = "SELECT * FROM (" + q   + ") LIMIT 0"

      dt = jsl.DataSource.__ni_db_metadata(ds.path, q)
      md = []
      for(var a = 0, l = dt.length; a < l; a++) {

        dta = dt[a]
        t   = dta.type && dta.type.toUpperCase()

             if(!t || t === "ANY")            t = "any"
        else if(t === "INT_P")                t = "i++"
        else if(t === "INT_NN")               t = "i+"
        else if(t === "REAL_P")               t = "n++"
        else if(t === "REAL_NN")              t = "n+"
        else if(t === "BOOL")                 t = "b"
        else if(t === "DATE")                 t = "d"
        else if(t === "XML")                  t = "xml"
        else if(t === "JSON")                 t = "json"
        else if(t === "TEXT_NE")              t = "s+"
        else if((/INT/).test(t))              t = "i"
        else if((/BLOB/).test(t))             t = "blob"
        else if((/CHAR|CLOB|TEXT/i).test(t))  t = "s"
        else                                  t = "n"

        md[a] = {
        	wr:     dta.wr,
          id:     dta.id,
          field:  dta.field,
          key:    dta.key,
          type:   t
        }

        v = dta.value

        if(v != null && v !== "null") {
          if(t === "b")                v = v === "1"
          else if(!v.search(/^x'/i))   v = jsl.require("data").Blob.HEX + v.slice(2, -1)
          else if(v.charAt(0) === "'") v = v.slice(1, -1).replace(/''/g, "'")
          else                         v = +v

          try       { md[a].value = jsl.DataSource.__fromNative(v, t, dta.id + "(default)") }
          catch(e)  { throw jsl.DataError("e.dst.db.BadMetadata", [ dta.id ], e) }
        }
      }

      md.tid  = dt.table
      md.wr   = dt.wr

      return md
    }
  }

  //register
  //-------------------------------------------------------------------------------
  jsl.DataSource.type("db", jsl.DataSource.CDb)
